using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using System.Reflection;
using Join2007;

namespace Join
{
    public partial class JoinControl : UserControl
    {
        public JoinControl()
        {
            InitializeComponent();
        }

        List<Excel.ListObject> ListObjectCollection = null;

        private void CheckCondition(bool b, string message)
        {
            if (!b)
            {
                throw new Exception(message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime startTime = DateTime.Now;

                GetTables();
                CheckCondition(CheckThatChoicesStillMatch(), "Choices don't match tables on active sheet");
                CheckCondition(checkedListBox1.CheckedItems.Count > 0, "Expect at least one checked field");

                Globals.ThisAddIn.Application.ScreenUpdating = false;

                SortListObject(ListObjectCollection[0], HeaderTable0);
                SortListObject(ListObjectCollection[1], HeaderTable1);

                Excel.Worksheet newWorkSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                WriteOutMergedHeader(newWorkSheet);

                int rowIndex = 1;


                foreach (KeyValuePair<Array, Array> row0AndRow1 in Join())
                {
                    ++rowIndex;
                    Array array = WriteOutMergedRow(row0AndRow1);
                    Excel.Range range = (Excel.Range)newWorkSheet.Cells[rowIndex, 1];
                    range = (Excel.Range)range.get_Resize(array.GetLength(0), array.GetLength(1));
                    range.Value2 = array;
                }


                //!!!put this after try
                Globals.ThisAddIn.Application.ScreenUpdating = true;

                //MessageBox.Show((DateTime.Now - startTime).ToString());
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }

        }

        private IEnumerable<KeyValuePair<Array, Array>> Join()
        {
            switch((string)comboBoxJoinType.SelectedItem)
            {
                case "Full Outer Join":
                    return FullOuterJoin();
                case "Left Outer Join":
                    return LeftOuterJoin();
                case "Right Outer Join":
                    return RightOuterJoin();
                case "Inner Join":
                    return InnerJoin();
                default:
                    Debug.Assert(false); // real assert
                    return FullOuterJoin();
            }
        }

        private void WriteOutMergedHeader(Excel.Worksheet newWorkSheet)
        {
            List<string> headerList = new List<string>();
            foreach (string header in checkedListBox1.CheckedItems)
            {
                headerList.Add(header);
            }
            AddOtherHeadersFromTable(ref headerList, ListObjectCollection[0].Name, HeaderTable0, HeaderTable1);
            AddOtherHeadersFromTable(ref headerList, ListObjectCollection[1].Name, HeaderTable1, HeaderTable0);

            for (int columnIndexBase0 = 0; columnIndexBase0 < headerList.Count; ++columnIndexBase0)
            {
                ((Excel.Range)newWorkSheet.Cells[1, columnIndexBase0 + 1]).set_Value(Missing.Value, headerList[columnIndexBase0]);
            }
        }

        private void AddOtherHeadersFromTable(ref List<string> headerList, string tableName, SortedDictionary<string, int> headerTableA, SortedDictionary<string, int> headerTableB)
        {
            foreach (string header in OrignalOrder(headerTableA))
            {
                if (!checkedListBox1.CheckedItemsIgnoringCaseContains(header))
                {
                    if (headerTableB.ContainsKey(header))
                    {
                        headerList.Add(tableName + "." + header);
                    }
                    else
                    {
                        headerList.Add(header);
                    }
                }
            }
        }



        private Array WriteOutMergedRow(KeyValuePair<Array, Array> row0AndRow1)
        {
            Array row0 = row0AndRow1.Key;
            Array row1 = row0AndRow1.Value;
            Debug.Assert(row0 != null || row1 != null); //real assert
            Array array = new object[1, HeaderTable0.Count + HeaderTable1.Count - checkedListBox1.CheckedItems.Count];

            int iColumn = 0;

            //!!!merge code
            foreach (string header in checkedListBox1.CheckedItems)
            {
                if (row0 != null)
                {
                    WriteOutRowValue(ref array, ref iColumn, row0, header, HeaderTable0);
                }
                else
                {
                    Debug.Assert(row1 != null); // real assert
                    WriteOutRowValue(ref array, ref iColumn, row1, header, HeaderTable1);
                }
            }
            foreach (string header in OrignalOrder(HeaderTable0))
            {
                if (!checkedListBox1.CheckedItemsIgnoringCaseContains(header))
                {
                    WriteOutRowValue(ref array,  ref iColumn, row0, header, HeaderTable0);
                }
            }
            foreach (string header in OrignalOrder(HeaderTable1))
            {
                if (!checkedListBox1.CheckedItemsIgnoringCaseContains(header))
                {
                    WriteOutRowValue(ref array,  ref iColumn, row1, header, HeaderTable1);
                }
            }
            return array;
        }

        private IEnumerable<string> OrignalOrder(SortedDictionary<string, int> headerTable)
        {
            //Instead of doing this on the fly (twice or more), could just keep two data structures
            string[] columnCollection = new List<string>(headerTable.Keys).ToArray();
            int[] positionCollection = new List<int>(headerTable.Values).ToArray();
            Array.Sort(positionCollection, columnCollection);
            return columnCollection;
        }

        private void WriteOutRowValue(ref Array array, ref int iColumn, Array row, string header, SortedDictionary<string, int> headerTable)
        {
            object value;
            if (null == row)
            {
                value = null;
            }
            else
            {
                value = row.GetValue(1, headerTable[header]);
            }

            array.SetValue(value, 0, iColumn);
            ++iColumn;
            //((Excel.Range)newWorkSheet.Cells[rowIndex, columnIndex]).set_Value(Missing.Value, value);
            
            //Debug.Write((value==null) ? "\t" : value.ToString() + "\t");
        }

        private void SortListObject(Excel.ListObject listObject, SortedDictionary<string, int> headerTable)
        {
            Excel.Sort sort = listObject.Sort;
            sort.SortFields.Clear();
            foreach (string header in checkedListBox1.CheckedItems)
            {
                int columnIndex = headerTable[header];
                sort.SortFields.Add(listObject.ListColumns[columnIndex].Range, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            sort.Apply();
        }

        private void JoinControl_VisibleChanged(object sender, EventArgs e)
        {
            if (this.Visible)
            {
                InitializeFromActiveWorksheet(/*active*/ false);
            }
        }

        private void InitializeFromActiveWorksheet(bool active)
        {
            checkedListBox1.Items.Clear();
            HeaderTable0 = null;
            HeaderTable1 = null;
            GetTables();

            if (ListObjectCollection.Count != 2)
            {
                CheckCondition(!active, "Expect two Excel 2007 tables on worksheet");
                return;
            }

            HeaderTable0 = CreateHeaderTable(ListObjectCollection[0]);
            HeaderTable1 = CreateHeaderTable(ListObjectCollection[1]);
            foreach(string header0 in OrignalOrder(HeaderTable0))
            {
                if (HeaderTable1.ContainsKey(header0))
                {
                    checkedListBox1.Items.Add(header0, true);
                }
            }
            CheckCondition(checkedListBox1.Items.Count > 0, "The two tables have no column names in common.");

        }
        SortedDictionary<string, int> HeaderTable0;
        SortedDictionary<string, int> HeaderTable1;

        private static SortedDictionary<string, int> CreateHeaderTable(Excel.ListObject listObject)
        {
            SortedDictionary<string, int> headerTable = new SortedDictionary<string, int>(StringComparer.CurrentCultureIgnoreCase);
            int indexBase1 = 0;
            foreach (Excel.Range header in (Excel.Range)listObject.HeaderRowRange)
            {
                ++indexBase1;
                headerTable.Add(header.Value2.ToString(), indexBase1);
            }
            return headerTable;
        }

        private void GetTables()
        {
            Excel.Worksheet worksheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
            CheckCondition(null != worksheet, "Expect an active worksheet");
            ListObjectCollection = new List<Excel.ListObject>();
            foreach (Excel.ListObject listObject in worksheet.ListObjects)
            {
                ListObjectCollection.Add(listObject);
            }

            if (ListObjectCollection.Count < 2)
            {
                return;
            }

            if ((double)ListObjectCollection[0].Range.Left > (double)ListObjectCollection[1].Range.Left
                || ((double)ListObjectCollection[0].Range.Left == (double)ListObjectCollection[1].Range.Left && (double)ListObjectCollection[0].Range.Top > (double)ListObjectCollection[1].Range.Top))
            {
                Excel.ListObject listObjectTemp = ListObjectCollection[0];
                ListObjectCollection[0] = ListObjectCollection[1];
                ListObjectCollection[1] = listObjectTemp;
            }
        }

        //!!!share some code with InitializeFromActiveWorksheet
        private bool CheckThatChoicesStillMatch()
        {
            if (ListObjectCollection.Count != 2)
            {
                return false;
            }

            return CheckHeaderTable(HeaderTable0, ListObjectCollection[0]) && CheckHeaderTable(HeaderTable1, ListObjectCollection[1]);
        }

        private bool CheckHeaderTable(SortedDictionary<string, int> headerTable, Excel.ListObject listObject)
        {
            int indexBase1 = 0;
            foreach (Excel.Range header in (Excel.Range)listObject.HeaderRowRange)
            {
                ++indexBase1;
                int oldIndexBase1;
                if (!headerTable.TryGetValue(header.Value2.ToString(), out oldIndexBase1))
                {
                    return false;
                }
                if (indexBase1 != oldIndexBase1)
                {
                    return false;
                }
            }
            if (indexBase1 != headerTable.Count)
            {
                return false;
            }

            return true;
        }

 

        //http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp
        private void FillComboBox(Excel.ListObject listObject, ComboBox comboBox)
        {
            foreach (Excel.Range header in (Excel.Range) listObject.HeaderRowRange)
            {
                comboBox.Items.Add(header.Value2);
            }
            if (comboBox.Items.Count > 0)
            {
                comboBox.SelectedIndex = 0;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                InitializeFromActiveWorksheet(/*active*/ true);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }

        private void JoinControl_Load(object sender, EventArgs e)
        {
            comboBoxJoinType.SelectedIndex = 0;
            //comboBoxJoinType.Items.Add(3);
        }

        private IEnumerable<KeyValuePair<Array, Array>> FullOuterJoin()
        {
            IEnumerator<Group> groupEnumerator0 = Group.GetCollection(ListObjectCollection[0].ListRows, HeaderTable0, checkedListBox1.CheckedItemsAsString()).GetEnumerator();
            IEnumerator<Group> groupEnumerator1 = Group.GetCollection(ListObjectCollection[1].ListRows, HeaderTable1, checkedListBox1.CheckedItemsAsString()).GetEnumerator();

            bool moveNext0 = groupEnumerator0.MoveNext();
            bool moveNext1 = groupEnumerator1.MoveNext();
            while (moveNext0 || moveNext1)
            {
                Group group0 = moveNext0 ? groupEnumerator0.Current : null;
                Group group1 = moveNext1 ? groupEnumerator1.Current : null;

                int iCompare = ICompareGroupsWithNull(moveNext0, moveNext1, group0, group1);

                switch (iCompare)
                {
                    case 0:
                        foreach (Array listRow0 in group0.ArrayList)
                        {
                            foreach (Array listRow1 in group1.ArrayList)
                            {
                                yield return new KeyValuePair<Array, Array>(listRow0, listRow1);
                            }
                        }

                        moveNext0 = groupEnumerator0.MoveNext();
                        moveNext1 = groupEnumerator1.MoveNext();
                        break;
                    case -1:
                        foreach (Array listRow0 in group0.ArrayList)
                        {
                            yield return new KeyValuePair<Array, Array>(listRow0, null);
                        }

                        moveNext0 = groupEnumerator0.MoveNext();
                        break;
                    case 1:
                        foreach (Array listRow1 in group1.ArrayList)
                        {
                            yield return new KeyValuePair<Array, Array>(null, listRow1);
                        }

                        moveNext1 = groupEnumerator1.MoveNext();
                        break;
                }

            }
        }

        private static int ICompareGroupsWithNull(bool moveNext0, bool moveNext1, Group group0, Group group1)
        {
            if (moveNext0)
            {
                if (moveNext1)
                {
                    return group0.CompareTo(group1);
                }
                else
                {
                    return -1;
                }
            }
            else
            {
                Debug.Assert(moveNext1);
                return 1;
            }
        }

        private IEnumerable<KeyValuePair<Array, Array>> InnerJoin()
        {
            foreach (KeyValuePair<Array, Array> outerPair in FullOuterJoin())
            {
                if (outerPair.Key != null && outerPair.Value != null)
                {
                    yield return outerPair;
                }
            }
        }

        private IEnumerable<KeyValuePair<Array, Array>> LeftOuterJoin()
        {
            foreach (KeyValuePair<Array, Array> outerPair in FullOuterJoin())
            {
                if (outerPair.Key != null)
                {
                    yield return outerPair;
                }
            }
        }

        private IEnumerable<KeyValuePair<Array, Array>> RightOuterJoin()
        {
            foreach (KeyValuePair<Array, Array> outerPair in FullOuterJoin())
            {
                if (outerPair.Value != null)
                {
                    yield return outerPair;
                }
            }
        }
    }


}
// Microsoft Research, eScience Research Group, Microsoft Public License (Ms-PL)
// Copyright (c) Microsoft Corporation. All rights reserved. 